//
//  DataBaseHandle.m
//  UI_18_DataBase
//
//  Created by FSD on 15/11/18.
//  Copyright © 2015年 apple. All rights reserved.
//

#import "DataBaseHandle.h"
#import <sqlite3.h>
@interface DataBaseHandle ()

@property (nonatomic, copy) NSString *dataBasePath;
@property (nonatomic, retain) NSMutableArray *dataSource;
@property (nonatomic, retain) NSMutableArray *searchSource;

@end

//定义全局变量 - 只保证存在一个数据库DataBaseHandle
static DataBaseHandle *dataBaseHandle = nil;

@implementation DataBaseHandle


#pragma mark - private
//获取数据库保存的路径
- (NSString *)dataBasePath {
    if (!_dataBasePath) {
        NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
        NSString *path = [paths lastObject];
        self.dataBasePath = [path stringByAppendingString:@"/myDB.sqlite"];
        
    }
    return _dataBasePath;
}

+ (instancetype)shareDataBase {
    
//    以这种方法保证整个工程无论什么时调用，都会返回相同的dataBaseHandle
    if (!dataBaseHandle) {
        dataBaseHandle = [[DataBaseHandle alloc] init];
    }
    return dataBaseHandle;
}

//数据库指针
static sqlite3 *db = nil;

//1.打开数据库
- (void)openDataBase {
//    打印路径
    NSLog(@"%@", self.dataBasePath);
//    参数1：数据库路径
//    参数2：数据库指针的地址
    
//    如果数据库存在就打开已经存在的数据库，如果数据库不存在则创建再打开
    int result = sqlite3_open(self.dataBasePath.UTF8String, &db);
    if (result == SQLITE_OK) {
        NSLog(@"数据库打开成功");
    } else {
        NSLog(@"数据库打开失败");
    }
}

//2.关闭数据库
- (void)closeDataBase {
    int result = sqlite3_close(db);
    if (result == SQLITE_OK) {
        NSLog(@"数据库关闭成功");
    } else {
        NSLog(@"数据库关闭失败");
    }
}

//3.创建表
- (void)createTable {
    //1.准备sql语句
    NSString *sqlString = @"create table if not exists collect(id integer primary key autoincrement, title text not null, image text, url text, kind text)";
    
    //2.执行sql语句
    int result = sqlite3_exec(db, sqlString.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"创建表格成功");
    } else {
        NSLog(@"创建表格失败");
    }
}

//4.插入数据
- (void)insertDataBase:(NSString *)title withImage:(NSString *)image withURL:(NSString *)url withKind:(NSString *)kind {
    //1.准备sql语句
    NSString *sqlString = @"insert into collect(title, image, url, kind) values(?, ?, ?, ?)";
    //2.获取伴随指针，用来绑定参数，获取数据 -- 数据库的状态指针
    sqlite3_stmt *stmt = NULL;
    //3.预执行
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        //4.绑定参数
        sqlite3_bind_text(stmt, 1, title.UTF8String, -1, NULL);
        sqlite3_bind_text(stmt, 2, image.UTF8String, -1, NULL);
        sqlite3_bind_text(stmt, 3, url.UTF8String, -1, NULL);
        sqlite3_bind_text(stmt, 4, kind.UTF8String, -1, NULL);

        
        //5.执行
        if (sqlite3_step(stmt) == SQLITE_DONE) {
            NSLog(@"插入成功");
        } else {
            NSLog(@"插入失败");
        }
    } else {
        NSLog(@"语句错误");
    }
    //6.关闭伴随指针
    sqlite3_finalize(stmt);
}

- (NSMutableArray *)dataSource {
    if (!_dataSource) {
        self.dataSource = [NSMutableArray arrayWithCapacity:0];
    }
    return _dataSource;
}

- (NSMutableArray *)searchSource {
    if (!_searchSource) {
        self.searchSource = [NSMutableArray arrayWithCapacity:0];
    }
    return _searchSource;
}

//查询
- (NSMutableArray *)selectDataBase:(NSString *)title {
    [self.dataSource removeAllObjects];
    NSString *sqlString;
    //1.准备sql语句
    if (title == nil) {
        sqlString = @"select title,image,url,kind from collect";
    } else {
        sqlString = @"select * from collect where title = ?";
    }
    
    //2.伴随指针
    sqlite3_stmt *stmt = NULL;
    
    //3.予执行
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        //4.绑定参数
        sqlite3_bind_text(stmt, 1, title.UTF8String, -1, NULL);
        
        //5.执行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            //从伴随指针获取数据
            NSString *selectTitle = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 0)];
            
            NSString *selectImage = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
            
            NSString *selectURL = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
            
            NSString *selectKind = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 3)];
            
            NSDictionary *dic = [NSDictionary dictionaryWithObjectsAndKeys:selectTitle, @"title", selectImage, @"image", selectURL, @"url", selectKind, @"kind", nil];
            [self.dataSource addObject:dic];

        }
        
    } else {
//        NSLog(@"语句错误");
    }
    
    //6.关闭伴随指针
    sqlite3_finalize(stmt);
    
    return self.dataSource;
}

- (NSMutableArray *)selectSearchDataBase:(NSString *)title {
    [self.searchSource removeAllObjects];
    NSString *sqlString;
    //1.准备sql语句
//    if (title == nil) {
//        sqlString = @"select title,image,url,kind from collect";
//    } else {
//        sqlString = @"select * from collect where title = ?";
//    }
    NSString *str = @"select title,image,url,kind from collect where title like";
    sqlString = [NSString stringWithFormat:@"%@'%%%@%%'", str, title];
//    NSLog(@"哈哈哈哈%@", sqlString);
    //2.伴随指针
    sqlite3_stmt *stmt = NULL;
    
    //3.予执行
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        //4.绑定参数
        sqlite3_bind_text(stmt, 1, title.UTF8String, -1, NULL);
        
        //5.执行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            //从伴随指针获取数据
            NSString *selectTitle = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 0)];
            
            NSString *selectImage = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
            
            NSString *selectURL = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
            
            NSString *selectKind = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 3)];
            
            NSDictionary *dic = [NSDictionary dictionaryWithObjectsAndKeys:selectTitle, @"title", selectImage, @"image", selectURL, @"url", selectKind, @"kind", nil];
            [self.searchSource addObject:dic];
            
            //            NSLog(@"获取的姓名是%@， 年龄是%ld", selectTitle,selectAge);
        }
        
        
    } else {
//        NSLog(@"语句错误");
    }
    
    //6.关闭伴随指针
    sqlite3_finalize(stmt);
    
    return self.searchSource;
}

//删除
- (void)deleteDataBase:(NSString *)title {
    // 1.sql
    NSString *sqlString = @"delete from collect where title = ?";
    // 2.伴随指针
    sqlite3_stmt *stmt = NULL;
    // 3.预执行
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        // 4.确定参数
        sqlite3_bind_text(stmt, 1, title.UTF8String, -1, NULL);
        // 5.执行
        if (sqlite3_step(stmt) == SQLITE_DONE) {
//            [KVNProgress showSuccessWithStatus:@"删除成功"];
//            NSLog(@"删除成功！！！");
        } else {
//            [KVNProgress showErrorWithStatus:@"删除失败"];
        }
    } else {
//        [KVNProgress showErrorWithStatus:@"删除失败"];
    }
    // 6.关闭伴随指针
    sqlite3_finalize(stmt);
}

//修改
- (void)updateDataBase:(NSInteger)age {
    //1.sql
    NSString *sqlString = @"update student set age = 20 where age = ?";
    //2.伴随指针
    sqlite3_stmt *stmt = NULL;
    //3.予执行
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        //4.确定参数
        sqlite3_bind_int64(stmt, 1, age);
        //5.执行
        if (sqlite3_step(stmt) == SQLITE_DONE) {
//            NSLog(@"更新成功");
        } else {
//            NSLog(@"更新失败");
        }
    } else {
//        NSLog(@"语句错误");
    }
    //6.关闭伴随指针
    sqlite3_finalize(stmt);
}

@end
